package com.example.springboot.common.utils.ExcelUtil;

import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;




@Data
public class ExcelUtil<T> {

    //文件保存位置
    public String filePath = "E:\\Download";

    // 文件名字
    public String fileName = "数据 ";

    // 实体对象
    public Class<T> clazz;

    //有参构造
    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }
    public ExcelUtil() {}

    // 导出Excel(将服务端生成的Excel数据传输到客户端下载)
    // HttpServletResponse 在这里起到了将服务端生成的Excel数据传输到客户端浏览器并触发文件下载的关键作用
    public void exportExcel(List<?> objectList, HttpServletResponse response) {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建一个excel底部的工作表
            Sheet sheet = workbook.createSheet("Data");

            // 行数
            int rowNum = 0;
            Row headerRow = sheet.createRow(rowNum++);

            // 写入表头，即属性名
            if (!objectList.isEmpty()) {
                Object firstObject = objectList.get(0);
                //用反射来获取属性
                Field[] fields = firstObject.getClass().getDeclaredFields();
                int colNum = 0;
                for (Field field : fields) {
                    Cell cell = headerRow.createCell(colNum++);
                    cell.setCellValue(field.getName());
                }

                // 写入数据，即属性值
                for (Object obj : objectList) {
                    Row row = sheet.createRow(rowNum++);
                    colNum = 0;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        Cell cell = row.createCell(colNum++);
                        try {
                            Object value = field.get(obj);
                            if (value instanceof String) {
                                cell.setCellValue((String) value);
                            } else if (value instanceof Integer) {
                                cell.setCellValue((Integer) value);
                            } else if (value instanceof Number) {
                                cell.setCellValue(((Number) value).doubleValue());
                            } else if (value instanceof LocalDateTime) {
                                // 格式化LocalDateTime为字符串
                                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                                String formattedDateTime = ((LocalDateTime) value).format(formatter);
                                cell.setCellValue(formattedDateTime);
                            }
                            // 添加其他类型数据的处理逻辑
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                            // 处理访问异常
                        }
                    }
                }
            }

            // 设置响应头信息，告诉浏览器该响应内容为一个文件
            String encodedFileName = URLEncoder.encode(fileName + System.currentTimeMillis() + ".xlsx", "UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"");

            // 将 Excel 数据写入 HttpServletResponse 输出流
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 导出Excel(直接把excel转成文件流，部署后没法使用)
    public Boolean exportExcelByLocal(List<?> objectList) {
        try (Workbook workbook = new XSSFWorkbook()) {
            //创建一个excel底部的工作表
            Sheet sheet = workbook.createSheet("Data");

            //行数
            int rowNum = 0;
            Row headerRow = sheet.createRow(rowNum++);

            // 写入表头，即属性名
            if (!objectList.isEmpty()) {
                Object firstObject = objectList.get(0);
                Field[] fields = firstObject.getClass().getDeclaredFields();
                int colNum = 0;
                for (Field field : fields) {
                    Cell cell = headerRow.createCell(colNum++);
                    cell.setCellValue(field.getName());
                }

                // 写入数据，即属性值
                for (Object obj : objectList) {
                    Row row = sheet.createRow(rowNum++);
                    colNum = 0;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        Cell cell = row.createCell(colNum++);
                        try {
                            Object value = field.get(obj);
                            if (value instanceof String) {
                                cell.setCellValue((String) value);
                            } else if (value instanceof Number) {
                                cell.setCellValue(((Number) value).doubleValue());
                            } else if (value instanceof LocalDateTime) {
                                // 格式化LocalDateTime为字符串
                                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                                String formattedDateTime = ((LocalDateTime) value).format(formatter);
                                cell.setCellValue(formattedDateTime);
                            }
                            // 添加其他类型数据的处理逻辑
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                            // 处理访问异常
                        }
                    }
                }
            }

            File file = new File(filePath, fileName + System.currentTimeMillis() + ".xlsx");
            try (FileOutputStream fileOut = new FileOutputStream(file)) {
                workbook.write(fileOut);
                return true; // 导出成功
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false; // 导出失败
    }

    public List<T> importExcel(InputStream inputStream) {
        List<T> objectList = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0); //数据在第一个工作表

            // 获取表头，即属性名
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }

            T obj = null;
            // 读取数据行并转换为对象
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row dataRow = sheet.getRow(i);
                obj = clazz.getDeclaredConstructor().newInstance();

                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = dataRow.getCell(j);
                    String header = headers.get(j);

                    Field field = clazz.getDeclaredField(header); // 属性名和表头名一致
                    field.setAccessible(true);

                    if (field.getType().equals(String.class)) {
                        field.set(obj, cell.getStringCellValue());
                    } else if (field.getType().equals(Long.class)) {
                        field.set(obj, (long) cell.getNumericCellValue());
                    } else if (field.getType().equals(Integer.class)) {
                        field.set(obj, (int) cell.getNumericCellValue());
                    } else if (field.getType().equals(LocalDateTime.class)) {
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        LocalDateTime dateTime = LocalDateTime.parse(cell.getStringCellValue(), formatter);
                        field.set(obj, dateTime);
                    }
                    // 添加其他属性类型的处理逻辑
                }

                objectList.add(obj);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return objectList;
    }
}
